﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using agvcontroller.Common;
using agvcontroller.Log;

namespace agvcontroller.Common.DataBase
{
    public class CAGVCDB
    {
        public static string TABLE_AGVCONTROLLER = "AGVController";
        public static string TABLE_ALARMLIST = "AlarmList";
        public static string TABLE_CHARGERTYPE = "ChargerType";
        public static string TABLE_EJECTTYPE = "EjectType";
        public static string TABLE_GUIDETYPE = "GuideType";
        public static string TABLE_NODETYPE = "NodeType";
        public static string TABLE_NODELIST = "NodeList";
        public static string TABLE_EDGELIST = "EdgeList";
        public static string TABLE_TERMINALLIST = "TerminalList";
        public static string TABLE_LOCKZONELIST = "LockZoneList";
        public static string TABLE_CROSSLIST = "CrossList";
        public static string TABLE_VEHICLELIST = "VehicleList";
        public static string TABLE_ACTIONTYPE= "ActionType";
        public static string TABLE_COMMTYPE= "CommType";

        #region members
        /// <summary> 공급자 명 </summary>
        private string ConnStr = "Data Source=localhost\\SQLEXPRESS; Database=AGVC; Integrated Security=SSPI; MultipleActiveResultSets=True";

        /// <summary> DB 연결 객체</summary>
        private SqlConnection SqlCon;

        /// <summary> Result를 얻어올 객체 </summary>
        private SqlDataReader sqlReader;

        /// <summary> DB 접속 여부 </summary>
        bool is_connection = false;

        public DataSet AGVCDataSet = new DataSet();

        SqlDataAdapter AdapterAGVC = new SqlDataAdapter();
        SqlDataAdapter AdapterAlarm = new SqlDataAdapter();
        SqlDataAdapter AdapterChagerType = new SqlDataAdapter();
        SqlDataAdapter AdapterEjectType = new SqlDataAdapter();
        SqlDataAdapter AdapterGuideType = new SqlDataAdapter();
        SqlDataAdapter AdapterNodeType = new SqlDataAdapter();
        SqlDataAdapter AdapterActionType = new SqlDataAdapter();
        SqlDataAdapter AdapterVehicleCommType = new SqlDataAdapter();
        SqlDataAdapter AdapterVehicleList = new SqlDataAdapter();
        SqlDataAdapter AdapterNodeList = new SqlDataAdapter();
        SqlDataAdapter AdapterEdgeList = new SqlDataAdapter();
        SqlDataAdapter AdapterCrossList = new SqlDataAdapter();
        SqlDataAdapter AdapterLockZoneList = new SqlDataAdapter();
        SqlDataAdapter AdapterTerminalList= new SqlDataAdapter();

        SqlCommandBuilder CBAGVC;
        SqlCommandBuilder CBAlarm;
        SqlCommandBuilder CBChargerType;
        SqlCommandBuilder CBEjectType;
        SqlCommandBuilder CBGuideType;
        SqlCommandBuilder CBActionType;
        SqlCommandBuilder CBNodeType;
        SqlCommandBuilder CBVehicleCommType;
        SqlCommandBuilder CBVehicleList;
        SqlCommandBuilder CBNodeList;
        SqlCommandBuilder CBEdgeList;
        SqlCommandBuilder CBCrossList;
        SqlCommandBuilder CBLockZoneList;
        SqlCommandBuilder CBTerminalList;

        Logger Log;
        #endregion

        /// <summary>
        /// 생성자
        /// </summary>
        public CAGVCDB()
        {
            Log = new Logger("DB");

            GetDataSet();
        }

        #region DB Method
        /// <summary>
        /// 객체의 인스턴스를 가져옵니다.
        /// </summary>
        /// <returns></returns>
        /// 
        private static CAGVCDB uniqueInstance;
        public static CAGVCDB getInstance()
        {
            if (uniqueInstance == null)
            {
                uniqueInstance = new CAGVCDB();
            }

            return uniqueInstance;
        }

        DataSet GetDataSet()
        {
            try
            {
                DB_Open();

                AdapterAGVC.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TABLE_AGVCONTROLLER), SqlCon);
                AdapterAGVC.UpdateCommand = new SqlCommand();
                AdapterAGVC.InsertCommand = new SqlCommand();
                CBAGVC = new SqlCommandBuilder(AdapterAGVC);
                AdapterAGVC.Fill(AGVCDataSet, TABLE_AGVCONTROLLER);

                AdapterAlarm.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TABLE_ALARMLIST), SqlCon);
                AdapterAlarm.UpdateCommand = new SqlCommand();
                AdapterAlarm.InsertCommand = new SqlCommand();
                CBAlarm = new SqlCommandBuilder(AdapterAlarm);
                AdapterAlarm.Fill(AGVCDataSet, TABLE_ALARMLIST);

                AdapterChagerType.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TABLE_CHARGERTYPE) ,SqlCon);
                AdapterChagerType.UpdateCommand = new SqlCommand();
                AdapterChagerType.InsertCommand = new SqlCommand();
                CBChargerType = new SqlCommandBuilder(AdapterChagerType);
                AdapterChagerType.Fill(AGVCDataSet, TABLE_CHARGERTYPE);

                AdapterChagerType.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TABLE_EJECTTYPE), SqlCon);
                AdapterChagerType.UpdateCommand = new SqlCommand();
                AdapterChagerType.InsertCommand = new SqlCommand();
                CBEjectType = new SqlCommandBuilder(AdapterChagerType);
                AdapterChagerType.Fill(AGVCDataSet, TABLE_EJECTTYPE);

                AdapterGuideType.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TABLE_GUIDETYPE), SqlCon);
                AdapterGuideType.UpdateCommand = new SqlCommand();
                AdapterGuideType.InsertCommand = new SqlCommand();
                CBGuideType = new SqlCommandBuilder(AdapterGuideType);
                AdapterGuideType.Fill(AGVCDataSet, TABLE_GUIDETYPE);

                AdapterActionType.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TABLE_ACTIONTYPE), SqlCon);
                AdapterActionType.UpdateCommand = new SqlCommand();
                AdapterActionType.InsertCommand = new SqlCommand();
                CBActionType = new SqlCommandBuilder(AdapterActionType);
                AdapterActionType.Fill(AGVCDataSet, TABLE_ACTIONTYPE);

                AdapterVehicleCommType.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TABLE_COMMTYPE), SqlCon);
                AdapterVehicleCommType.UpdateCommand = new SqlCommand();
                AdapterVehicleCommType.InsertCommand = new SqlCommand();
                CBVehicleCommType = new SqlCommandBuilder(AdapterVehicleCommType);
                AdapterVehicleCommType.Fill(AGVCDataSet, TABLE_COMMTYPE);

                AdapterNodeType.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TABLE_NODETYPE), SqlCon);
                AdapterNodeType.UpdateCommand = new SqlCommand();
                AdapterNodeType.InsertCommand = new SqlCommand();
                CBNodeType = new SqlCommandBuilder(AdapterNodeType);
                AdapterNodeType.Fill(AGVCDataSet, TABLE_NODETYPE);

                AdapterVehicleList.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TABLE_VEHICLELIST), SqlCon);
                AdapterVehicleList.UpdateCommand = new SqlCommand();
                AdapterVehicleList.InsertCommand = new SqlCommand();
                CBVehicleList = new SqlCommandBuilder(AdapterVehicleList);
                AdapterVehicleList.Fill(AGVCDataSet, TABLE_VEHICLELIST);

                AdapterNodeList.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TABLE_NODELIST), SqlCon);
                AdapterNodeList.UpdateCommand = new SqlCommand();
                AdapterNodeList.InsertCommand = new SqlCommand();
                CBNodeList = new SqlCommandBuilder(AdapterNodeList);
                AdapterNodeList.Fill(AGVCDataSet, TABLE_NODELIST);

                AdapterEdgeList.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TABLE_EDGELIST), SqlCon);
                AdapterEdgeList.UpdateCommand = new SqlCommand();
                AdapterEdgeList.InsertCommand = new SqlCommand();
                CBEdgeList = new SqlCommandBuilder(AdapterEdgeList);
                AdapterEdgeList.Fill(AGVCDataSet, TABLE_EDGELIST);

                AdapterCrossList.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TABLE_CROSSLIST), SqlCon);
                AdapterCrossList.UpdateCommand = new SqlCommand();
                AdapterCrossList.InsertCommand = new SqlCommand();
                CBCrossList = new SqlCommandBuilder(AdapterCrossList);
                AdapterCrossList.Fill(AGVCDataSet, "CrossList");

                AdapterLockZoneList.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TABLE_LOCKZONELIST), SqlCon);
                AdapterLockZoneList.UpdateCommand = new SqlCommand();
                AdapterLockZoneList.InsertCommand = new SqlCommand();
                CBLockZoneList = new SqlCommandBuilder(AdapterLockZoneList);
                AdapterLockZoneList.Fill(AGVCDataSet, "LockZoneList");

                AdapterTerminalList.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}",TABLE_TERMINALLIST), SqlCon);
                AdapterTerminalList.UpdateCommand = new SqlCommand();
                AdapterTerminalList.InsertCommand = new SqlCommand();
                CBTerminalList= new SqlCommandBuilder(AdapterTerminalList);
                AdapterTerminalList.Fill(AGVCDataSet, TABLE_TERMINALLIST);

                DB_Close();
            }
            catch (Exception ex)
            {
                Log.Exception(string.Format("{0},{1}", ex.Message, ex.StackTrace));
            }

            return AGVCDataSet;
        }

        /// <summary>
        /// DB Open 여부를 return
        /// </summary>
        public bool Is_Open()
        {
            return is_connection;
        }

        /// <summary>
        /// DB Open
        /// </summary>
        /// <param name="_DBPath">dbm 파일 주소</param>
        /// <returns>Oepn 여부</returns>
        public bool DB_Open()
        {
            Log.Info("DB_Open");

            /// SQL 클래스 선언
            SqlCon = new SqlConnection();

            /// Connecting 할 공급자와 파일경로를 선택한다.
            SqlCon.ConnectionString = ConnStr;

            try
            {
                /// SQL 연결을 시작한다
                SqlCon.Open();

                is_connection = true;

                Console.WriteLine("DB Open()");

                return true;
            }
            catch
            {
                /// 중간에 실패시 다시 닫아준다.
                SqlCon.Close();

                is_connection = false;

                return false;
            }
        }

        /// <summary>
        /// DB 닫기
        /// </summary>
        /// <returns>Close 여부</returns>
        public bool DB_Close()
        {
            Log.Info("DB_Close");

            if (Is_Open())
            {
                Console.WriteLine("DB_Close()");

                SqlCon.Close();

                is_connection = false;

                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 쿼리 실행 update,insert,delete query 위주로 사용 내부적으로 호출예정
        /// </summary>
        /// <param name="query">실행할 쿼리</param>
        /// <returns>쿼리 실행 여부</returns>
        public bool ExcuteQuery(string query)
        {
            Log.Info("ExcuteQuery (" + query + ")");

            try
            {
                if (Is_Open())
                {
                    /// 쿼리를 실행 시키기 위한 명령 클래스
                    SqlCommand db = new SqlCommand(query, SqlCon);

                    sqlReader = null;

                    /// 실행 시킨 결과를 DAO_Result에 담는다.
                    sqlReader = db.ExecuteReader();

                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex.StackTrace);

                return false;
            }
        }

        /// <summary>
        /// 쿼리 실행후의 Record가 존재하는지 여부
        /// </summary>
        /// <returns>Result Record의 존재 여부</returns>
        public bool IsResultRecord()
        {
            Log.Info("IsResultRecord");

            try
            {
                if (Is_Open())
                {
                    return sqlReader.HasRows;
                }
                return false;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 다음 레코드로 넘어감
        /// </summary>
        /// <returns> 레코드가 있는지 여부</returns>
        public bool Next()
        {
            Log.Info("Next");

            try
            {
                if (Is_Open() && IsResultRecord())
                {
                    return sqlReader.Read();
                }
                return false;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 해당필드의 레코드값을 가져옴
        /// </summary>
        /// <param name="Field_Name">필드 이름</param>
        /// <returns>필드 값 String형</returns>
        public string Get_Result(string Field_Name)
        {
            Log.Info("Get_Result (" + Field_Name + ")");

            try
            {
                if (Is_Open())
                {
                    return sqlReader[Field_Name].ToString();
                }
                return null;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 해당필드번호의 레코드값을 가져옴
        /// </summary>
        /// <param name="Field_Order">필드 번호</param>
        /// <returns>필드 값 String형</returns>
        public string Get_Result(int Field_Order)
        {
            Log.Info("Get_Result (" + Field_Order + ")");

            try
            {
                if (Is_Open())
                {
                    return sqlReader[Field_Order].ToString();
                }
                return null;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        //public Dictionary<string, stPosition> GetVehicleInfo()
        //{
        //    Dictionary<string, stPosition> dicPos = new Dictionary<string, stPosition>();
        //    Log.Info("Get_Result (unit=Buffer");

        //    try
        //    {
        //        ExcuteQuery("SELECT * FROM TB_POSITION WHERE (Unit='" + _unit + "')");
        //        while (Next())
        //        {
        //            stPosition pos = new stPosition();
        //            pos.unit = sqlReader[(int)enumPosition.Unit].ToString();
        //            pos.position = sqlReader[(int)enumPosition.Position].ToString();
        //            pos.X = Convert.ToInt32(sqlReader[(int)enumPosition.X]);
        //            pos.Y = Convert.ToInt32(sqlReader[(int)enumPosition.Y]);
        //            pos.T = Convert.ToInt32(sqlReader[(int)enumPosition.T]);
        //            pos.Z = Convert.ToInt32(sqlReader[(int)enumPosition.Z]);
        //            pos.X_time = Convert.ToInt32(sqlReader[(int)enumPosition.X_TIME]);
        //            pos.Y_time = Convert.ToInt32(sqlReader[(int)enumPosition.Y_TIME]);
        //            pos.T_time = Convert.ToInt32(sqlReader[(int)enumPosition.T_TIME]);
        //            pos.Z_time = Convert.ToInt32(sqlReader[(int)enumPosition.Z_TIME]);

        //            dicPos.Add(pos.position, pos);
        //        }
        //    }
        //    catch
        //    {
        //        return null;
        //    }

        //    return dicPos;
        //}



        #endregion DBMethod
    }

    //public class CCAGVCDB
    //{
    //    string ConnectString;
    //    public DataSet AGVCDataSet = new DataSet();
    //    SqlDataAdapter AGVCAdapter = new SqlDataAdapter();

    //    /// <summary> DB 접속 여부 </summary>
    //    bool is_connection = false;

    //    public CCAGVCDB()
    //    {
    //        InitDB();
    //    }

    //    public int InitDB()
    //    {
    //        ConnectString = "Data Source=localhost\\SQLEXPRESS; Database=AGVC; Integrated Security=SSPI; MultipleActiveResultSets=True";
    //        SqlConnection conn = new SqlConnection(ConnectString);
    //        conn.Open();

    //        string query = "SELECT * From * AGVController";
    //        AGVCAdapter.SelectCommand = new SqlCommand(query, conn);
    //        AGVCAdapter.Fill(AGVCDataSet);

    //        query = "SELECT * From * AlarmList";
    //        AGVCAdapter.SelectCommand = new SqlCommand(query, conn);
    //        AGVCAdapter.Fill(AGVCDataSet);

    //        query = "SELECT * From * ChargerType";
    //        AGVCAdapter.SelectCommand = new SqlCommand(query, conn);
    //        AGVCAdapter.Fill(AGVCDataSet); 
             
    //        conn.Close();

    //        return CReturnCode.SUCCESS;
    //    }

    //    /// <summary>
    //    /// DB Open 여부를 return
    //    /// </summary>
    //    public bool Is_Open()
    //    {
    //        return is_connection;
    //    }
    //}
}


